I have some tables that contain fields with double quotes in them, but they are part of a name or abbreviation for "inches", the " do NOT escape the field.
I need to export this to a CSV file but MySQL keeps breaking the fields at the " and creating extra columns in the CSV. How can I handle " within a field value?
Code:
select
makers.maker_name,
baits.bait,
depth_line.depth,
depth_line.lineout a
from makers
inner join baits on makers.maker_id = baits.maker_id
inner join depth_line on depth_line.bait_id = baits.bait_id
order by
makers.maker_name asc,
baits.bait,
depth_line.depth asc
into outfile '/tmp/muskie.csv'
fields terminated by ',';
Sample data from the baits table:
wchiquito :
I can't reproduce the problem.\n\nmysql> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 5.7.12 |\n+-----------+\n1 row in set (0.00 sec)\n\nmysql> DROP TABLE IF EXISTS `table0`;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> CREATE TABLE IF NOT EXISTS `table0` (\n -> `column0` VARCHAR(255) DEFAULT NULL,\n -> `column1` VARCHAR(255) DEFAULT NULL\n -> );\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> INSERT INTO `table0`\n -> (`column0`, `column1`)\n -> VALUES\n -> ('\"inches\"', 'inches'),\n -> ('4\"', 'Lure name, 8\", char, char'),\n -> ('\"VALUE 1\"', 'NEW VALUE 1');\nQuery OK, 3 rows affected (0.00 sec)\nRecords: 3 Duplicates: 0 Warnings: 0\n\nmysql> SELECT\n -> `column0`,\n -> `column1`\n -> FROM\n -> `table0`\n -> INTO OUTFILE '/path/to/file/muskie.csv'\n -> FIELDS TERMINATED BY ',';\nQuery OK, 3 rows affected (0.00 sec)\n\n\nFile: /path/to/file/muskie.csv\n\n\"inches\",inches\n4\",Lure name\\, 8\"\\, char\\, char\n\"VALUE 1\",NEW VALUE 1\n\n\nUPDATE\n\nPerhaps it can be useful to add the parameter OPTIONALLY ENCLOSED BY '\"':\n\nSELECT\n `column0`,\n `column1`\nFROM\n `table0`\nINTO OUTFILE '/path/to/file/muskie.csv'\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';\n\n\nFile: /path/to/file/muskie.csv\n\n\"\\\"inches\\\"\",\"inches\"\n\"4\\\"\",\"Lure name, 8\\\", char, char\"\n\"\\\"VALUE 1\\\"\",\"NEW VALUE 1\"\n",
2016-04-29T15:11:45